package com.project.dao.impl;

import com.project.bean.CommodityBean;
import com.project.bean.CutPageBean;
import com.project.dao.ICommodityDao;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
@Repository
public class CommodityDaoImpl extends BaseDao implements ICommodityDao {
    @Override
    public CutPageBean<CommodityBean> findByItem(Integer pageNO, String name) {
        CutPageBean<CommodityBean> cutBean = new CutPageBean<>();
        String sql ="select p.*,count(s.pk_commentId) commentNum from t_commodity p left join t_comment s on p.pk_commodityId =s.fk_commodityId ";
        String dynaSql = " where 1=1 ";
        if (name!=null && name.length()!=0){
            dynaSql +="and p_name like '%"+name+"%'";
        }

        this.setConnection();
        try {
            ps=con.prepareStatement(sql+dynaSql+" group by p.pk_commodityId limit ?,?");
            ps.setObject(1,(pageNO - 1)*CutPageBean.PAGESIZE);
            ps.setObject(2,CutPageBean.PAGESIZE);
            rs=ps.executeQuery();

            while(rs.next()){
                cutBean.getList().add(this.getCommodity());
            }
            ps = con.prepareStatement("select count(*) c from t_commodity "+dynaSql);
            rs = ps.executeQuery();
            if(rs.next()) {
                cutBean.setCount(rs.getInt("c"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            this.close();
        }

        return cutBean;
    }

    private CommodityBean getCommodity() throws SQLException {
        CommodityBean commodity = new CommodityBean();
        commodity.setId(rs.getInt("pk_commodityId"));
        commodity.setName(rs.getString("c_name"));
        commodity.setPrice(rs.getInt("c_price"));
        commodity.setImgPath(rs.getString("c_imgPath"));
        commodity.setCommentNum(rs.getInt("commentNum"));
        return commodity;
    }

    @Override
    public CommodityBean findById(Integer id) {
        CommodityBean commodity = null;
        this.setConnection();
        try {
            ps=con.prepareStatement("select p.*,count(s.pk_commentId) commentNum from t_commodity p left join t_comment s on p.pk_commodityId =s.fk_commodityId where p.pk_commodityId=? ");
            ps.setObject(1,id);
            rs=ps.executeQuery();
            if (rs.next()){
                commodity=this.getCommodity();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            this.close();
        }
        return commodity;
    }
}
